import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql


if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/transportationdata?charset=utf8")
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='transportationdata', charset='utf8')
    # 车牌过滤
    plateData = pd.read_sql("select * from plate_table where Plate_Num!='-'", con=conn)
    FreeFlowTimeData = pd.read_sql("select * from free_flow_time_table", con=conn)
    flowData = pd.read_sql("select * from flow_table", con=conn)
    junctionRelationData = pd.read_sql("select * from junction_relation_table", con=conn)
    junctionRelationData["free_flow_time"] = np.inf
    # 延误时间计算
    cols = ["plate_num", "cur", "direction", "pre", "delay_time"]
    delayTimeData = pd.DataFrame(columns=cols)
    # 根据车牌分组
    for name, group in plateData.groupby("plate_num"):
        # 过滤数量不足的数据
        if len(group) <= 1:
            continue
        # 根据时间逆序
        group = group.sort_values(by="pass_time", ascending=False)
        # 顺序访问前后两行数据
        for i in range(1, len(group)):
            curData = group.iloc[i - 1]
            preData = group.iloc[i]

            cur = curData["monitor_id"]
            direction = curData["direction"]
            pre = preData["monitor_id"]
            # 前后路口一致，数据无意义
            if cur == pre:
                continue
            interval = pd.to_datetime(curData["pass_time"]) - pd.to_datetime(preData["PassTime"])
            interval = interval.total_seconds()
            # 时间间隔小于30s，数据无意义
            if interval < 30:
                continue
            res = FreeFlowTimeData[(FreeFlowTimeData["cur"] == cur) & (FreeFlowTimeData["direction"] == direction) & (FreeFlowTimeData["pre"] == pre)]

            if len(res) == 1:
                # 延误时间等于间隔时间减自由流时间
                delayTime = interval - res["free_flow_time"].iloc[0]
                series = pd.Series((name, cur, direction, pre, delayTime), index=cols)
                delayTimeData = delayTimeData.append(series, ignore_index=True)
                print(delayTime)

    # 方向的平均延误时间计算
    meanDelayTimeData = pd.DataFrame(delayTimeData.groupby(["cur", "direction", "pre"], as_index=False)["delayTime"].mean())
    meanDelayTimeData = meanDelayTimeData.rename(columns={"delay_time": "mean_delay_Time"})
    # 方向的总延误时间计算
    directionDelayTimeData = meanDelayTimeData.merge(flowData, on=["cur", "direction"], how="left")
    directionDelayTimeData["delay_time"] = directionDelayTimeData["mean_delay_Time"] * directionDelayTimeData["flow"]
    directionDelayTimeData.to_sql("direction_delay_time_table", con=engine, if_exists="replace", index=False)
    # 路口的总延误时间计算
    directionDelayTimeData = directionDelayTimeData.drop(columns="mean_delay_Time")
    junctionDelayTimeData = pd.DataFrame(directionDelayTimeData.groupby("cur", as_index=False).sum())
    junctionDelayTimeData.to_sql("junction_delay_time_table", con=engine, if_exists="replace", index=False)